package com.example.mapper;

import com.example.pojo.Area_code;
import com.example.pojo.Poetry;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface PoetryLocateMapper {
    @Select("select * from poetry where code=#{location_id}")
    List<Poetry> locate_and_poetry(int location_id);
    @Select("select * from poetry where code like CONCAT(#{location_id},'%')")
    List<Poetry> locate_all_poetry(int location_id);
        @Select("select * from poetry where id=#{id}")
    Poetry poetry_all(int id);
//    @Update("create view poe_loc as select * from poetry where poetry.code=#{location_id}")
//    void create_view(int location_id);
//    @Select("select * from poe_loc where code=#{location_id} ")
//    List<Poetry> select_view(int location_id);
//    @Update("drop view if exists poe_loc")
//    void drop_view();
//    @Update("drop index index_code on poetry")
//    void drop_index();
    @Update("create index index_id on poetry(id);")
    void create_index();
    @Update("alter table poetry modify column code int;")
    void alter_code();
    @Select("select url from poetry where id=#{id}")
    String detail_url(int id);
    @Select("select count(id) from poetry")
    int count_poetry();
    @Select("select * from area_code where area_name like CONCAT('%',#{key},'%')")
    List<Area_code> search_area_name(String key);
    @Select("select * from poetry where poetry_name like CONCAT('%',#{key},'%')")
    List<Poetry> search_poetry_name(String key);
    @Select("select code from area_code where code like CONCAT(#{location_id},'%')")
    List<String> province_below(int location_id);
    @Select("select code from area_code")
    List<String> select_area();
    @Select("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'poetry_information' AND TABLE_NAME = 'area_code' AND COLUMN_NAME = 'poetry_num';")
    String check_column();
    @Update("alter table area_code add poetry_num int default 0")
    void add_column_num();
    @Update("update area_code set poetry_num=#{poetry_num} where code=#{code}")
    void add_num(String code,int poetry_num);
    @Select("select poetry_num from area_code where code=#{code}")
    int get_locate_poetry(String code);
    @Delete("delete from area_code where poetry_num=0")
    void delete_area();
}
